package org.tgcloud.group.service.manager.export;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.tgcloud.group.core.BeanUtil;
import org.tgcloud.group.core.DateUtil;
import org.tgcloud.group.entity.MjMediumOffline;
import org.tgcloud.group.repository.MjMediumOfflineMapper;
import org.tgcloud.group.repository.SysLabelValueMapper;
import org.tgcloud.group.web.controller.dto.export.mediumDateCount.MediumDateCountContentTypeExportDTO;
import org.tgcloud.group.web.controller.dto.export.mediumDateCount.MediumDateCountExportDTO;
import org.tgcloud.group.web.controller.dto.medium.MediumProjectMsgDTO;
import org.tgcloud.group.web.controller.dto.sys.SysLabelValueListDTO;

import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.time.YearMonth;
import java.time.ZoneId;
import java.util.*;

/**
 * @Author: zhy
 * @Created: 2024-06-13-下午1:44
 * @Description:
 */
@Service
public class MediumDateCountExportManageService {


    public static String[] headList = {"月份", "上刊总天数", "广告位总天数", "上刊占比",
            "站点", "上刊总天数", "广告位总天数", "占比",
            "内容分类", "上刊总天数", "占比"};

    public static String[] headValueList = {"monthName", "monthSubTotalDayNum", "monthADTotalDayNum", "monthSubRate",
            "stationType", "stationTypeSubTotalDayNum", "stationTypeADTotalDayNum", "stationTypeSubRate",
            "subContentType", "subContentTypeSubTotalDayNum", "subContentTypeSubRate"};

    public static Integer[] mergeList = {0, 0, 0, 0,
            0, 0, 0, 0,
            -1, -1, -1};


    public static String[] head2List = {"月份", "上刊总天数", "广告位总天数", "上刊占比",
            "内容分类", "内容上刊总天数", "占比"};

    public static String[] headValue2List = {"monthName", "monthSubTotalDayNum", "monthADTotalDayNum", "monthSubRate",
            "contentType", "contentTypeSubTotalDayNum", "contentTypeSubRate"};

    public static Integer[] merge2List = {0, 0, 0, 0,
            0, 0, 0};

    public static Integer[] mergeMonthIndexList = {0, 1, 2, 3};

    public static Integer[] noSetMonthIndexList = {0, 1, 2, 3}; // 默认重复月份不赋值

    public static Integer[] noSetStationAndMonthIndexList = {8, 9, 10}; // 默认重复月份与站点不赋值


    public static String[] headMonthList = {"月份", "上刊总天数", "广告位总天数", "上刊占比",
            "站点",
            "内容分类", "上刊总天数", "占比"
            , "上刊总天数", "广告位总天数", "占比"};

    public static String[] headValueMonthList = {"monthName", "monthSubTotalDayNum", "monthADTotalDayNum", "monthSubRate",
            "stationType",
            "subContentType", "subContentTypeSubTotalDayNum", "subContentTypeSubRate"
            , "stationTypeSubTotalDayNum", "stationTypeADTotalDayNum", "stationTypeSubRate"};

    @Autowired
    private SysLabelValueMapper sysLabelValueMapper;

    @Autowired
    private MjMediumOfflineMapper mediumOfflineMapper;

    public void doCreateExcel(OutputStream out, Long startTime, Long endTime, String excelName) throws Exception{
        long startCountTime = System.currentTimeMillis();
        System.out.println("开始时间------" + startCountTime);
        List<MediumDateCountExportDTO> dtos = getCountExportList(startTime, endTime);
        List<YearMonth> yearMonthList = DateUtil.getMonthListByStartTimeAndEndTime(new Date(startTime), new Date(endTime));
        List<JSONObject> dtoList = new ArrayList<>();
        Map<String, MediumDateCountExportDTO> contentTypeMap = new TreeMap<>();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMM");
        dtos.forEach(dto -> {
            JSONObject jsonObject = (JSONObject) JSONObject.toJSON(dto);
            dtoList.add(jsonObject);
            contentTypeMap.put(sdf.format(dto.getMonth()) + ":" + dto.getContentTypeId(), dto);
        });
        List<JSONObject> contentTypeList = new ArrayList<>();
        contentTypeMap.forEach((key, value) -> {
            JSONObject jsonObject = (JSONObject) JSONObject.toJSON(value);
            contentTypeList.add(jsonObject);
        });
        getExportListExcel(excelName, out, dtoList, contentTypeList, yearMonthList);
        long endCountTime = System.currentTimeMillis();
        System.out.println("结束时间------" + endCountTime);
        System.out.println("相差时间------" + (startCountTime - endCountTime));
    };



    // yMergeNum 从上往下合并的行数
    // yMergeNumToMonth 月份的合并行数
    // yearMonthSize 月份的数量
    public void getExportListExcel(String title, OutputStream out,
                                   List<JSONObject> exportList, List<JSONObject> contentTypeList,
                                   List<YearMonth> yearMonthList) throws Exception{
        List<SysLabelValueListDTO> sysLabelValueList43DTOS = sysLabelValueMapper.selectListBySysLabelId(43, null, null);
//        List<SysLabelValueListDTO> sysLabelValueList31DTOS = sysLabelValueMapper.selectListBySysLabelId(31, null, null);
        List<MjMediumOffline> mjMediumOfflines = mediumOfflineMapper.selectAllListByCount();
        int yMergeNum = sysLabelValueList43DTOS.size() + 1;
        int yMergeNumToMonth = new BigDecimal(yMergeNum).multiply(new BigDecimal(mjMediumOfflines.size())).intValue();
        int yMergeNumTo31 = new BigDecimal(yMergeNumToMonth).divide(new BigDecimal(yMergeNum), 0, BigDecimal.ROUND_HALF_UP).intValue();
        //声明一个工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        doWriteSheet1(workbook, "站点汇总", out, exportList, yMergeNum, yMergeNumToMonth, yMergeNumTo31);
        //生成一个表格
        doWriteSheet2(workbook, "内容分类汇总", out, contentTypeList, yMergeNum, yMergeNum, yMergeNumTo31);
        for (int i = 0;i<yearMonthList.size();i++){
            YearMonth yearMonth = yearMonthList.get(i);
            Date dateStringByYearMonth = DateUtil.getDateStringByYearMonth(yearMonth);
            String monthName = DateUtil.getMMByDate(dateStringByYearMonth) + "月";
            List<JSONObject> dtoList = new ArrayList<>();
            for (JSONObject jsonObject : exportList){
                if (monthName.equals(jsonObject.getString("monthName"))){
                    dtoList.add(jsonObject);
                }
            }
            doWriteSheet1ByYear(workbook, monthName, out, dtoList);
        }
        try {
            workbook.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public void doWriteSheet1(XSSFWorkbook workbook, String title, OutputStream out,
                              List<JSONObject> exportList, int yMergeNum, int yMergeNumToMonth, int yMergeNumTo31){
        XSSFSheet sheet = workbook.createSheet(title);
        //设置表格默认列宽度为15个字符
        sheet.setDefaultColumnWidth(12);
        sheet.setDefaultRowHeight((short) 10);
        sheet.setDefaultRowHeightInPoints(10);

        // 生成并设置另一个样式,用于设置内容样式
        XSSFCellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
        // 填充效果（全景填充）
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置边框
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setBorderRight(BorderStyle.THIN);
        headerStyle.setBorderTop(BorderStyle.THIN);
        // 生成另一个字体
        XSSFFont font2 = workbook.createFont();
        font2.setColor(IndexedColors.BLACK.getIndex());
        font2.setBold(true);
        font2.setFontHeightInPoints((short) 11);
        font2.setFontName("微软雅黑");
        headerStyle.setFont(font2);

        XSSFFont sureTypeFont = workbook.createFont();
        sureTypeFont.setColor(IndexedColors.BLACK.getIndex());
        sureTypeFont.setFontHeightInPoints((short) 11);
        sureTypeFont.setFontName("微软雅黑");


        List<String> headerList = new ArrayList<>();
        Arrays.stream(headList).forEach(headerList::add);

        List<Integer> mergeIndexList = new ArrayList<>();
        Arrays.stream(mergeList).forEach(mergeIndexList::add);

        Integer rowNum = 1;
        XSSFRow row = sheet.createRow(rowNum);
        row.setHeightInPoints(20);
        for (int i = 0; i < headerList.size(); i++){
            XSSFCell cell = row.createCell(i);
            cell.setCellStyle(headerStyle);
            XSSFRichTextString text = new XSSFRichTextString(headerList.get(i));
            cell.setCellValue(text);
        }
        if (!BeanUtil.isNullList(exportList)){
            for (JSONObject dto : exportList) {
                rowNum = rowNum + 1;
                row = sheet.createRow(rowNum);
                for (int z = 0; z < headValueList.length; z++) {
                    String key = headValueList[z];
                    Object o = dto.getObject(key, Object.class);
                    XSSFCell cell = row.createCell(z);
                    if (o != null){
                        String v = o.toString();
                        cell.setCellValue(v);
                        if (o instanceof BigDecimal){
                            cell.setCellValue(v + "%");
                        }else if (o instanceof Integer){
                            if (JSON.toJSONString(o).equals("0E-8")) {
                                cell.setCellValue(0);
                            } else if (JSON.toJSONString(o).equals("0E-24")) {
                                cell.setCellValue(0);
                            } else {
                                cell.setCellValue(((Number) o).longValue());
                            }
                        }
                    }else {
                        cell.setCellValue("");
                    }
                    if (mergeIndexList.get(z) != -1 && rowNum >= 2){
                        Integer lastRowNum = mergeIndexList.get(z);
                        Integer mergeNum = yMergeNum;
                        for (Integer monthIndexKey : mergeMonthIndexList){
                            if (z == monthIndexKey){
                                mergeNum = yMergeNumToMonth;
                            }
                        }
                        if (lastRowNum == 0 || rowNum > (lastRowNum + mergeNum)){
                            CellRangeAddress region = new CellRangeAddress(rowNum, rowNum + mergeNum - 1, z, z);
                            sheet.addMergedRegion(region);
                            mergeIndexList.set(z, rowNum - 1);
                        }
                    }
                }
            }
        }
    }


    public void doWriteSheet2(XSSFWorkbook workbook, String title, OutputStream out,
                              List<JSONObject> exportList, int yMergeNum, int yMergeNumToMonth, int yMergeNumTo31){
        XSSFSheet sheet = workbook.createSheet(title);
        //设置表格默认列宽度为15个字符
        sheet.setDefaultColumnWidth(12);
        sheet.setDefaultRowHeight((short) 10);
        sheet.setDefaultRowHeightInPoints(10);

        // 生成并设置另一个样式,用于设置内容样式
        XSSFCellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
        // 填充效果（全景填充）
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置边框
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setBorderRight(BorderStyle.THIN);
        headerStyle.setBorderTop(BorderStyle.THIN);
        // 生成另一个字体
        XSSFFont font2 = workbook.createFont();
        font2.setColor(IndexedColors.BLACK.getIndex());
        font2.setBold(true);
        font2.setFontHeightInPoints((short) 11);
        font2.setFontName("微软雅黑");
        headerStyle.setFont(font2);

        XSSFFont sureTypeFont = workbook.createFont();
        sureTypeFont.setColor(IndexedColors.BLACK.getIndex());
        sureTypeFont.setFontHeightInPoints((short) 11);
        sureTypeFont.setFontName("微软雅黑");


        List<String> headerList = new ArrayList<>();
        Arrays.stream(head2List).forEach(headerList::add);

        List<Integer> mergeIndexList = new ArrayList<>();
        Arrays.stream(merge2List).forEach(mergeIndexList::add);


        Integer rowNum = 1;
        XSSFRow row = sheet.createRow(rowNum);
        row.setHeightInPoints(20);
        for (int i = 0; i < headerList.size(); i++){
            XSSFCell cell = row.createCell(i);
            cell.setCellStyle(headerStyle);
            XSSFRichTextString text = new XSSFRichTextString(headerList.get(i));
            cell.setCellValue(text);
        }
        if (!BeanUtil.isNullList(exportList)){
            for (JSONObject dto : exportList) {
                rowNum = rowNum + 1;
                row = sheet.createRow(rowNum);
                for (int z = 0; z < headValue2List.length; z++) {
                    String key = headValue2List[z];
                    Object o = dto.getObject(key, Object.class);
                    XSSFCell cell = row.createCell(z);
                    if (o != null){
                        String v = o.toString();
                        cell.setCellValue(v);
                        if (o instanceof BigDecimal){
                            cell.setCellValue(v + "%");
                        }else if (o instanceof Integer){
                            if (JSON.toJSONString(o).equals("0E-8")) {
                                cell.setCellValue(0);
                            } else if (JSON.toJSONString(o).equals("0E-24")) {
                                cell.setCellValue(0);
                            } else {
                                cell.setCellValue(((Number) o).longValue());
                            }
                        }
                    }else {
                        cell.setCellValue("");
                    }
                    if (mergeIndexList.get(z) != -1 && rowNum >= 2){
                        Integer lastRowNum = mergeIndexList.get(z);
                        Integer mergeNum = 1;
                        for (Integer monthIndexKey : mergeMonthIndexList){
                            if (z == monthIndexKey){
                                mergeNum = yMergeNumToMonth;
                            }
                        }
                        if ((lastRowNum == 0 || rowNum > (lastRowNum + mergeNum)) && mergeNum > 1){
                            CellRangeAddress region = new CellRangeAddress(rowNum, rowNum + mergeNum - 1, z, z);
                            sheet.addMergedRegion(region);
                            mergeIndexList.set(z, rowNum - 1);
                        }
                    }
                }
            }
        }
    }



    public void doWriteSheet1ByYear(XSSFWorkbook workbook, String title, OutputStream out,
                              List<JSONObject> exportList){
        XSSFSheet sheet = workbook.createSheet(title);
        //设置表格默认列宽度为15个字符
        sheet.setDefaultColumnWidth(12);
        sheet.setDefaultRowHeight((short) 10);
        sheet.setDefaultRowHeightInPoints(10);

        // 生成并设置另一个样式,用于设置内容样式
        XSSFCellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
        // 填充效果（全景填充）
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置边框
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setBorderRight(BorderStyle.THIN);
        headerStyle.setBorderTop(BorderStyle.THIN);
        // 生成另一个字体
        XSSFFont font2 = workbook.createFont();
        font2.setColor(IndexedColors.BLACK.getIndex());
        font2.setBold(true);
        font2.setFontHeightInPoints((short) 11);
        font2.setFontName("微软雅黑");
        headerStyle.setFont(font2);

        XSSFFont sureTypeFont = workbook.createFont();
        sureTypeFont.setColor(IndexedColors.BLACK.getIndex());
        sureTypeFont.setFontHeightInPoints((short) 11);
        sureTypeFont.setFontName("微软雅黑");


        List<String> headerList = new ArrayList<>();
        Arrays.stream(headMonthList).forEach(headerList::add);

        Integer rowNum = 1;
        XSSFRow row = sheet.createRow(rowNum);
        row.setHeightInPoints(20);
        for (int i = 0; i < headerList.size(); i++){
            XSSFCell cell = row.createCell(i);
            cell.setCellStyle(headerStyle);
            XSSFRichTextString text = new XSSFRichTextString(headerList.get(i));
            cell.setCellValue(text);
        }
        if (!BeanUtil.isNullList(exportList)){
            for (int i=0;i<exportList.size();i++) {
                JSONObject dto = exportList.get(i);
                rowNum = rowNum + 1;
                row = sheet.createRow(rowNum);
                for (int z = 0; z < headValueMonthList.length; z++) {
                    String key = headValueMonthList[z];
                    Object o = dto.getObject(key, Object.class);
                    XSSFCell cell = row.createCell(z);
                    if (o != null){
                        String v = o.toString();
                        cell.setCellValue(v);
                        if (o instanceof BigDecimal){
                            cell.setCellValue(v + "%");
                        }else if (o instanceof Integer){
                            if (JSON.toJSONString(o).equals("0E-8")) {
                                cell.setCellValue(0);
                            } else if (JSON.toJSONString(o).equals("0E-24")) {
                                cell.setCellValue(0);
                            } else {
                                cell.setCellValue(((Number) o).longValue());
                            }
                        }
                        if (rowNum > 2 && i > 0){
                            if (exportList.get(i).get("monthName").equals(exportList.get(i - 1).get("monthName"))){
                                for (Integer monthIndex : noSetMonthIndexList){
                                    if (z == monthIndex){
                                        cell.setCellValue("");
                                    }
                                }
                                if (exportList.get(i).get("monthName").equals(exportList.get(i - 1).get("monthName"))
                                && exportList.get(i).get("stationId").equals(exportList.get(i - 1).get("stationId"))){
                                    for (Integer monthIndex : noSetStationAndMonthIndexList){
                                        if (z == monthIndex){
                                            cell.setCellValue("");
                                        }
                                    }
                                }
                            }
                        }
                    }else {
                        cell.setCellValue("");
                    }
                }
                if (rowNum > 2 && i > 0 && (i + 1) < exportList.size()
                        && exportList.get(i).get("monthName").equals(exportList.get(i + 1).get("monthName"))
                        && !exportList.get(i).get("stationId").equals(exportList.get(i + 1).get("stationId"))){
                    rowNum = rowNum + 1;
                }
            }
        }
    }



    public List<MediumDateCountExportDTO> getCountExportListOld(Long startTime, Long endTime) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMM");
        List<SysLabelValueListDTO> sysLabelValueListDTOS = sysLabelValueMapper.selectListBySysLabelId(43, null, null);
        Map<String, String> contentTypeMap = new HashMap();
        contentTypeMap.put("0", "项目");
        sysLabelValueListDTOS.forEach(dto -> {
            contentTypeMap.put(dto.getId().toString(), dto.getSysValueName());
        });
        List<JSONObject> contentList = new ArrayList<>();
        contentTypeMap.forEach((key, value) -> {
            JSONObject jsonObject = new JSONObject();
            jsonObject.put("id", key);
            jsonObject.put("name", value);
            contentList.add(jsonObject);
        });
        List<SysLabelValueListDTO> sysLabelValueList31DTOS = sysLabelValueMapper.selectListBySysLabelId(31, null, null);
        Map<String, String> stationTypeMap = new HashMap();
        sysLabelValueList31DTOS.forEach(dto -> {
            stationTypeMap.put(dto.getId().toString(), dto.getSysValueName());
        });
        Map<String, BigDecimal> contentTypeADCountMap = new HashMap<>();
        Map<String, BigDecimal> subTotalMap = new HashMap(); // 上刊总天数，key为（月:站点分类id:内容分类id）
        List<MjMediumOffline> mjMediumOfflines = mediumOfflineMapper.selectAllListByCount();
        List<MediumProjectMsgDTO> projectLists = mediumOfflineMapper.selectOfflineAllProjectList(new Date(startTime), new Date((endTime)));
        List<MediumDateCountExportDTO> monthCountList = new ArrayList<>();
        List<YearMonth> monthListByStartTimeAndEndTime = DateUtil.getMonthListByStartTimeAndEndTime(new Date(startTime), new Date(endTime));
        for (YearMonth yearMonth : monthListByStartTimeAndEndTime){
            int monthDayNum = yearMonth.lengthOfMonth(); // 获取当前月的天数
            Date dateStringByYearMonth = DateUtil.getDateStringByYearMonth(yearMonth);
            MediumDateCountExportDTO dto = new MediumDateCountExportDTO();
            dto.setMonth(dateStringByYearMonth);
            dto.setMonthName(DateUtil.getMMByDate(dateStringByYearMonth) + "月");
            dto.setContentTypeAddNum(0);
            Integer monthTotalDayNum = 0;
            Map<String, BigDecimal> stationTypeADCountMap = new HashMap<>();
            for (MjMediumOffline mediumOffline : mjMediumOfflines){
                Integer oneDayNum = mediumOffline.getNum();
                BigDecimal monthShowDayNum = new BigDecimal(monthDayNum).multiply(new BigDecimal(oneDayNum)); // 月总天数
                monthTotalDayNum = monthTotalDayNum + monthShowDayNum.intValue();
                if (stationTypeADCountMap.containsKey(mediumOffline.getExcelShowTitleId().toString())){
                    stationTypeADCountMap.put(mediumOffline.getExcelShowTitleId().toString(), stationTypeADCountMap.get(mediumOffline.getExcelShowTitleId().toString()).add(monthShowDayNum));
                }else {
                    stationTypeADCountMap.put(mediumOffline.getExcelShowTitleId().toString(), monthShowDayNum);
                }
            }
            dto.setMonthADTotalDayNum(monthTotalDayNum);
            for (String stationKey : stationTypeMap.keySet()){
                dto.setStationId(Integer.valueOf(stationKey));
                dto.setStationType(stationTypeMap.get(stationKey));
                if (stationTypeADCountMap.containsKey(stationKey)){
                    dto.setStationTypeADTotalDayNum(stationTypeADCountMap.get(stationKey).intValue());
                }else {
                    dto.setStationTypeADTotalDayNum(0);
                }
                for (String contentTypeKey : contentTypeMap.keySet()){
                    // 计算出每个月的所有行数，用所有行数除以内容分类个数，然后取整，就是当前内容分类在当前月的行数
                    BigDecimal totalContentNum = new BigDecimal(stationTypeMap.size()).multiply(new BigDecimal(contentTypeMap.size()));
                    BigDecimal contentTypeAddNum = totalContentNum.divide(new BigDecimal(contentTypeMap.size()), 0, BigDecimal.ROUND_DOWN);
                    BigDecimal contentTypeAddIndex = new BigDecimal(dto.getContentTypeAddNum()).divide(contentTypeAddNum, 0, BigDecimal.ROUND_DOWN);
                    JSONObject jsonObject = contentList.get(contentTypeAddIndex.intValue());
                    dto.setContentTypeId(Integer.valueOf(jsonObject.getString("id")));
                    dto.setContentType(jsonObject.getString("name"));
                    dto.setContentTypeAddNum(dto.getContentTypeAddNum() + 1);
                    dto.setSubContentTypeId(Integer.valueOf(contentTypeKey));
                    dto.setSubContentType(contentTypeMap.get(contentTypeKey));
                    monthCountList.add(JSON.parseObject(JSON.toJSONString(dto), MediumDateCountExportDTO.class));
                    for (MediumProjectMsgDTO projectMsgDTO : projectLists){
                        // 根据选择的时间，查询出是否满足是该月份的
                        if (DateUtil.getyyyyMM(projectMsgDTO.getChooseTime()).equals(DateUtil.getyyyyMM(dateStringByYearMonth))
                                || DateUtil.getyyyyMM(projectMsgDTO.getChooseEndTime()).equals(DateUtil.getyyyyMM(dateStringByYearMonth))) {
                            // 符合时间条件的，就进行统计，如果涉及到跨月，那么从1号开始或者从月底结束
                            Date chooseStartTime = projectMsgDTO.getChooseTime();
                            Date chooseEndTime = projectMsgDTO.getChooseEndTime();
                            Date modelStartTime = Date.from(yearMonth.atDay(1).atStartOfDay(ZoneId.systemDefault()).toInstant());
                            Date modelEndTime = Date.from(yearMonth.atEndOfMonth().atStartOfDay(ZoneId.systemDefault()).toInstant());
                            if (chooseStartTime.before(modelStartTime)) {
                                chooseStartTime = modelStartTime;
                            }
                            if (chooseEndTime.after(modelEndTime)) {
                                chooseEndTime = modelEndTime;
                            }
                            List<Date> dateListByStartTimeAndEndTime = DateUtil.getDateListByStartTimeAndEndTime(chooseStartTime, chooseEndTime);
                            String key = sdf.format(dateStringByYearMonth) + ":" + stationKey + ":";
                            String lastKey = "0";
                            if (projectMsgDTO.getProjectType() == 1) {
                                lastKey = "0";
                            } else {
                                lastKey = projectMsgDTO.getShowProjectTypeId().toString();
                            }
                            key = key + lastKey;
                            if (stationKey.equals(projectMsgDTO.getExcelShowTitleId().toString()) && lastKey.equals(contentTypeKey)) {
                                if (subTotalMap.containsKey(key)) {
                                    subTotalMap.put(key, subTotalMap.get(key).add(new BigDecimal(dateListByStartTimeAndEndTime.size())));
                                } else {
                                    subTotalMap.put(key, new BigDecimal(dateListByStartTimeAndEndTime.size()));
                                }
                                String contentTypeADKey = sdf.format(dateStringByYearMonth) + ":" + projectMsgDTO.getShowProjectTypeId();
                                if (contentTypeADCountMap.containsKey(contentTypeADKey)){
                                    contentTypeADCountMap.put(contentTypeADKey, contentTypeADCountMap.get(contentTypeADKey).add(new BigDecimal(dateListByStartTimeAndEndTime.size())));
                                }else {
                                    contentTypeADCountMap.put(contentTypeADKey, new BigDecimal(dateListByStartTimeAndEndTime.size()));
                                }
                            }
                        }
                    }
                }
            }
        }
        Map<String, BigDecimal> totalCountMap = new HashMap();
        for (String key : subTotalMap.keySet()){
            String[] split = key.split(":");
            if (totalCountMap.containsKey(split[0])){
                totalCountMap.put(split[0], totalCountMap.get(split[0]).add(subTotalMap.get(key)));
            }else {
                totalCountMap.put(split[0], subTotalMap.get(key));
            }
            if (totalCountMap.containsKey(split[0] + ":" + split[1])){
                totalCountMap.put(split[0] + ":" + split[1], totalCountMap.get(split[0] + ":" + split[1]).add(subTotalMap.get(key)));
            }else {
                totalCountMap.put(split[0] + ":" + split[1], subTotalMap.get(key));
            }
        }
        for (MediumDateCountExportDTO dto : monthCountList){
            if (subTotalMap.containsKey(sdf.format(dto.getMonth()) + ":" + dto.getStationId() + ":" + dto.getSubContentTypeId())){
                int subTotalDayNum = subTotalMap.get(sdf.format(dto.getMonth()) + ":" + dto.getStationId() + ":" + dto.getSubContentTypeId()).intValue();
                dto.setSubContentTypeSubTotalDayNum(subTotalDayNum);
                BigDecimal divide = BigDecimal.ZERO;
                if (dto.getStationTypeADTotalDayNum() > 0){
                    divide = new BigDecimal(subTotalDayNum).divide(new BigDecimal(dto.getStationTypeADTotalDayNum()), 4, BigDecimal.ROUND_HALF_UP);
                }
                dto.setSubContentTypeSubRate(divide.multiply(new BigDecimal(100)).setScale(2, BigDecimal.ROUND_HALF_UP));
            }
            if (totalCountMap.containsKey(sdf.format(dto.getMonth()))){
                dto.setMonthSubTotalDayNum(totalCountMap.get(sdf.format(dto.getMonth())).intValue());
                BigDecimal divide = BigDecimal.ZERO;
                if (dto.getMonthADTotalDayNum() > 0){
                    divide = new BigDecimal(totalCountMap.get(sdf.format(dto.getMonth())).intValue()).divide(new BigDecimal(dto.getMonthADTotalDayNum()), 4, BigDecimal.ROUND_HALF_UP);
                }
                dto.setMonthSubRate(divide.multiply(new BigDecimal(100)).setScale(2, BigDecimal.ROUND_HALF_UP));
            }
            if (totalCountMap.containsKey(sdf.format(dto.getMonth()) + ":" + dto.getStationId())){
                dto.setStationTypeSubTotalDayNum(totalCountMap.get(sdf.format(dto.getMonth()) + ":" + dto.getStationId()).intValue());
                BigDecimal divide = BigDecimal.ZERO;
                if (dto.getStationTypeADTotalDayNum() > 0){
                    divide = new BigDecimal(totalCountMap.get(sdf.format(dto.getMonth()) + ":" + dto.getStationId()).intValue()).divide(new BigDecimal(dto.getStationTypeADTotalDayNum()), 4, BigDecimal.ROUND_HALF_UP);
                }
                dto.setStationTypeSubRate(divide.multiply(new BigDecimal(100)).setScale(2, BigDecimal.ROUND_HALF_UP));
            }
            // 计算内容分类的统计上刊总天数
            if (contentTypeADCountMap.containsKey(sdf.format(dto.getMonth()) + ":" + dto.getContentTypeId())){
                BigDecimal bigDecimal = contentTypeADCountMap.get(sdf.format(dto.getMonth()) + ":" + dto.getContentTypeId());
                dto.setContentTypeSubTotalDayNum(bigDecimal.intValue());
                BigDecimal divide = BigDecimal.ZERO;
                if (dto.getMonthADTotalDayNum() > 0){
                    divide = bigDecimal.divide(new BigDecimal(dto.getMonthADTotalDayNum()), 4, BigDecimal.ROUND_HALF_UP);
                }
                dto.setContentTypeSubRate(divide.multiply(new BigDecimal(100)).setScale(2, BigDecimal.ROUND_HALF_UP));
            }
        }
        return monthCountList;
    }


    public List<MediumDateCountExportDTO> getCountExportList(Long startTime, Long endTime) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMM");
        List<SysLabelValueListDTO> sysLabelValueListDTOS = sysLabelValueMapper.selectListBySysLabelId(43, null, null);
        Map<String, String> contentTypeMap = new HashMap();
        contentTypeMap.put("0", "项目");
        sysLabelValueListDTOS.forEach(dto -> {
            contentTypeMap.put(dto.getId().toString(), dto.getSysValueName());
        });
        List<JSONObject> contentList = new ArrayList<>();
        contentTypeMap.forEach((key, value) -> {
            JSONObject jsonObject = new JSONObject();
            jsonObject.put("id", key);
            jsonObject.put("name", value);
            contentList.add(jsonObject);
        });
//        List<SysLabelValueListDTO> sysLabelValueList31DTOS = sysLabelValueMapper.selectListBySysLabelId(31, null, null);
        Map<String, String> stationTypeMap = new HashMap();
//        sysLabelValueList31DTOS.forEach(dto -> {
//            stationTypeMap.put(dto.getId().toString(), dto.getSysValueName());
//        });
        Map<String, BigDecimal> contentTypeADCountMap = new HashMap<>();
        Map<String, BigDecimal> subTotalMap = new HashMap(); // 上刊总天数，key为（月:站点分类id:内容分类id）
        List<MjMediumOffline> mjMediumOfflines = mediumOfflineMapper.selectAllListByCount();
        mjMediumOfflines.forEach(dto -> {
            stationTypeMap.put(dto.getId().toString(), dto.getName());
        });
        List<MediumProjectMsgDTO> projectLists = mediumOfflineMapper.selectOfflineAllProjectList(new Date(startTime), new Date((endTime)));
        List<MediumDateCountExportDTO> monthCountList = new ArrayList<>();
        List<YearMonth> monthListByStartTimeAndEndTime = DateUtil.getMonthListByStartTimeAndEndTime(new Date(startTime), new Date(endTime));
        for (YearMonth yearMonth : monthListByStartTimeAndEndTime){
            int monthDayNum = yearMonth.lengthOfMonth(); // 获取当前月的天数
            Date dateStringByYearMonth = DateUtil.getDateStringByYearMonth(yearMonth);
            MediumDateCountExportDTO dto = new MediumDateCountExportDTO();
            dto.setMonth(dateStringByYearMonth);
            dto.setMonthName(DateUtil.getMMByDate(dateStringByYearMonth) + "月");
            dto.setContentTypeAddNum(0);
            Integer monthTotalDayNum = 0;
            Map<String, BigDecimal> stationTypeADCountMap = new HashMap<>();
            for (MjMediumOffline mediumOffline : mjMediumOfflines){
                Integer oneDayNum = mediumOffline.getNum();
                BigDecimal monthShowDayNum = new BigDecimal(monthDayNum).multiply(new BigDecimal(oneDayNum)); // 月总天数
                monthTotalDayNum = monthTotalDayNum + monthShowDayNum.intValue();
                if (stationTypeADCountMap.containsKey(mediumOffline.getId().toString())){
                    stationTypeADCountMap.put(mediumOffline.getId().toString(), stationTypeADCountMap.get(mediumOffline.getId().toString()).add(monthShowDayNum));
                }else {
                    stationTypeADCountMap.put(mediumOffline.getId().toString(), monthShowDayNum);
                }
            }
            dto.setMonthADTotalDayNum(monthTotalDayNum);
            for (String stationKey : stationTypeMap.keySet()){
                dto.setStationId(Integer.valueOf(stationKey));
                dto.setStationType(stationTypeMap.get(stationKey));
                if (stationTypeADCountMap.containsKey(stationKey)){
                    dto.setStationTypeADTotalDayNum(stationTypeADCountMap.get(stationKey).intValue());
                }else {
                    dto.setStationTypeADTotalDayNum(0);
                }
                for (String contentTypeKey : contentTypeMap.keySet()){
                    // 计算出每个月的所有行数，用所有行数除以内容分类个数，然后取整，就是当前内容分类在当前月的行数
                    BigDecimal totalContentNum = new BigDecimal(stationTypeMap.size()).multiply(new BigDecimal(contentTypeMap.size()));
                    BigDecimal contentTypeAddNum = totalContentNum.divide(new BigDecimal(contentTypeMap.size()), 0, BigDecimal.ROUND_DOWN);
                    BigDecimal contentTypeAddIndex = new BigDecimal(dto.getContentTypeAddNum()).divide(contentTypeAddNum, 0, BigDecimal.ROUND_DOWN);
                    JSONObject jsonObject = contentList.get(contentTypeAddIndex.intValue());
                    dto.setContentTypeId(Integer.valueOf(jsonObject.getString("id")));
                    dto.setContentType(jsonObject.getString("name"));
                    dto.setContentTypeAddNum(dto.getContentTypeAddNum() + 1);
                    dto.setSubContentTypeId(Integer.valueOf(contentTypeKey));
                    dto.setSubContentType(contentTypeMap.get(contentTypeKey));
                    monthCountList.add(JSON.parseObject(JSON.toJSONString(dto), MediumDateCountExportDTO.class));
                    for (MediumProjectMsgDTO projectMsgDTO : projectLists){
                        // 根据选择的时间，查询出是否满足是该月份的
                        if (DateUtil.getyyyyMM(projectMsgDTO.getChooseTime()).equals(DateUtil.getyyyyMM(dateStringByYearMonth))
                                || DateUtil.getyyyyMM(projectMsgDTO.getChooseEndTime()).equals(DateUtil.getyyyyMM(dateStringByYearMonth))) {
                            // 符合时间条件的，就进行统计，如果涉及到跨月，那么从1号开始或者从月底结束
                            Date chooseStartTime = projectMsgDTO.getChooseTime();
                            Date chooseEndTime = projectMsgDTO.getChooseEndTime();
                            Date modelStartTime = Date.from(yearMonth.atDay(1).atStartOfDay(ZoneId.systemDefault()).toInstant());
                            Date modelEndTime = Date.from(yearMonth.atEndOfMonth().atStartOfDay(ZoneId.systemDefault()).toInstant());
                            if (chooseStartTime.before(modelStartTime)) {
                                chooseStartTime = modelStartTime;
                            }
                            if (chooseEndTime.after(modelEndTime)) {
                                chooseEndTime = modelEndTime;
                            }
                            List<Date> dateListByStartTimeAndEndTime = DateUtil.getDateListByStartTimeAndEndTime(chooseStartTime, chooseEndTime);
                            String key = sdf.format(dateStringByYearMonth) + ":" + stationKey + ":";
                            String lastKey = "0";
                            if (projectMsgDTO.getProjectType() == 1) {
                                lastKey = "0";
                            } else {
                                lastKey = projectMsgDTO.getShowProjectTypeId().toString();
                            }
                            key = key + lastKey;
                            if (stationKey.equals(projectMsgDTO.getLineId().toString()) && lastKey.equals(contentTypeKey)) {
                                if (subTotalMap.containsKey(key)) {
                                    subTotalMap.put(key, subTotalMap.get(key).add(new BigDecimal(dateListByStartTimeAndEndTime.size())));
                                } else {
                                    subTotalMap.put(key, new BigDecimal(dateListByStartTimeAndEndTime.size()));
                                }
                                String contentTypeADKey = sdf.format(dateStringByYearMonth) + ":" + projectMsgDTO.getShowProjectTypeId();
                                if (contentTypeADCountMap.containsKey(contentTypeADKey)){
                                    contentTypeADCountMap.put(contentTypeADKey, contentTypeADCountMap.get(contentTypeADKey).add(new BigDecimal(dateListByStartTimeAndEndTime.size())));
                                }else {
                                    contentTypeADCountMap.put(contentTypeADKey, new BigDecimal(dateListByStartTimeAndEndTime.size()));
                                }
                            }
                        }
                    }
                }
            }
        }
        Map<String, BigDecimal> totalCountMap = new HashMap();
        for (String key : subTotalMap.keySet()){
            String[] split = key.split(":");
            if (totalCountMap.containsKey(split[0])){
                totalCountMap.put(split[0], totalCountMap.get(split[0]).add(subTotalMap.get(key)));
            }else {
                totalCountMap.put(split[0], subTotalMap.get(key));
            }
            if (totalCountMap.containsKey(split[0] + ":" + split[1])){
                totalCountMap.put(split[0] + ":" + split[1], totalCountMap.get(split[0] + ":" + split[1]).add(subTotalMap.get(key)));
            }else {
                totalCountMap.put(split[0] + ":" + split[1], subTotalMap.get(key));
            }
        }
        for (MediumDateCountExportDTO dto : monthCountList){
            if (subTotalMap.containsKey(sdf.format(dto.getMonth()) + ":" + dto.getStationId() + ":" + dto.getSubContentTypeId())){
                int subTotalDayNum = subTotalMap.get(sdf.format(dto.getMonth()) + ":" + dto.getStationId() + ":" + dto.getSubContentTypeId()).intValue();
                dto.setSubContentTypeSubTotalDayNum(subTotalDayNum);
                BigDecimal divide = BigDecimal.ZERO;
                if (dto.getStationTypeADTotalDayNum() > 0){
                    divide = new BigDecimal(subTotalDayNum).divide(new BigDecimal(dto.getStationTypeADTotalDayNum()), 4, BigDecimal.ROUND_HALF_UP);
                }
                dto.setSubContentTypeSubRate(divide.multiply(new BigDecimal(100)).setScale(2, BigDecimal.ROUND_HALF_UP));
            }
            if (totalCountMap.containsKey(sdf.format(dto.getMonth()))){
                dto.setMonthSubTotalDayNum(totalCountMap.get(sdf.format(dto.getMonth())).intValue());
                BigDecimal divide = BigDecimal.ZERO;
                if (dto.getMonthADTotalDayNum() > 0){
                    divide = new BigDecimal(totalCountMap.get(sdf.format(dto.getMonth())).intValue()).divide(new BigDecimal(dto.getMonthADTotalDayNum()), 4, BigDecimal.ROUND_HALF_UP);
                }
                dto.setMonthSubRate(divide.multiply(new BigDecimal(100)).setScale(2, BigDecimal.ROUND_HALF_UP));
            }
            if (totalCountMap.containsKey(sdf.format(dto.getMonth()) + ":" + dto.getStationId())){
                dto.setStationTypeSubTotalDayNum(totalCountMap.get(sdf.format(dto.getMonth()) + ":" + dto.getStationId()).intValue());
                BigDecimal divide = BigDecimal.ZERO;
                if (dto.getStationTypeADTotalDayNum() > 0){
                    divide = new BigDecimal(totalCountMap.get(sdf.format(dto.getMonth()) + ":" + dto.getStationId()).intValue()).divide(new BigDecimal(dto.getStationTypeADTotalDayNum()), 4, BigDecimal.ROUND_HALF_UP);
                }
                dto.setStationTypeSubRate(divide.multiply(new BigDecimal(100)).setScale(2, BigDecimal.ROUND_HALF_UP));
            }
            // 计算内容分类的统计上刊总天数
            if (contentTypeADCountMap.containsKey(sdf.format(dto.getMonth()) + ":" + dto.getContentTypeId())){
                BigDecimal bigDecimal = contentTypeADCountMap.get(sdf.format(dto.getMonth()) + ":" + dto.getContentTypeId());
                dto.setContentTypeSubTotalDayNum(bigDecimal.intValue());
                BigDecimal divide = BigDecimal.ZERO;
                if (dto.getMonthADTotalDayNum() > 0){
                    divide = bigDecimal.divide(new BigDecimal(dto.getMonthADTotalDayNum()), 4, BigDecimal.ROUND_HALF_UP);
                }
                dto.setContentTypeSubRate(divide.multiply(new BigDecimal(100)).setScale(2, BigDecimal.ROUND_HALF_UP));
            }
        }
        return monthCountList;
    }



}
